
-- FUNCTION: public.str_sin_signos(text)

/*

********************************************************
ESTE SCRIPT DEBE SER CORRIDO MANUALMENTE SI ES NECESARIO
AL ACTIVAR TIENDANUBE/ML en un cliente ESTÁ ACA PARA QUE
NO SE PIERDA
******************************************************** 





DROP SCHEMA IF EXISTS tienda_ecommerce CASCADE;

CREATE SCHEMA  tienda_ecommerce
    AUTHORIZATION abacouser;


-- DROP FUNCTION public.str_sin_signos(text);

CREATE OR REPLACE FUNCTION public.str_sin_signos(
	text)
    RETURNS character varying
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$

DECLARE
CadenaEntrada ALIAS FOR $1;
vCadenaSalida varchar;
BEGIN
vCadenaSalida:= CadenaEntrada;

vCadenaSalida:= replace(vCadenaSalida, '>', '');    --O con acento al revés
vCadenaSalida:= replace(vCadenaSalida, '<', '');    --O con acento al revés

RETURN vCadenaSalida;

END;
$BODY$;

ALTER FUNCTION public.str_sin_signos(text)
    OWNER TO abacouser;
	
	-- FUNCTION: public.utf8_to_latin1(text)

-- DROP FUNCTION public.utf8_to_latin1(text);

CREATE OR REPLACE FUNCTION public.utf8_to_latin1(
	input_text text)
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
BEGIN
  RETURN convert_from(convert_to(input_text, 'UTF8'), 'LATIN1');
END;
$BODY$;

ALTER FUNCTION public.utf8_to_latin1(text)
    OWNER TO abacouser;


--------------------------------------
-- FUNCTION: tienda_ecommerce.get_cadenavacia(character varying, character varying)

-- DROP FUNCTION tienda_ecommerce.get_cadenavacia(character varying, character varying);

CREATE OR REPLACE FUNCTION tienda_ecommerce.get_cadenavacia(
	pdummy character varying,
	pdummy2 character varying)
    RETURNS TABLE(xml character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 100

AS $BODY$
DECLARE
BEGIN
  RETURN QUERY EXECUTE ('SELECT CAST(''FIN'' AS VARCHAR(64000)) as xml');
END;
$BODY$;

ALTER FUNCTION tienda_ecommerce.get_cadenavacia(character varying, character varying)
    OWNER TO abacouser;

----------------------------------------

CREATE OR REPLACE FUNCTION tienda_ecommerce.get_idtiendastocksucursal(
	ptienda character varying,
	pcodambsuc character varying)
    RETURNS character varying
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
  UltimaOrden varchar;
  id_tienda   varchar;
BEGIN
  -- vale retornar NULL
  id_tienda := case when pTienda = 'TN' then 
                   (SELECT SUBSTRING(notas, position('<TiendaStockTN>' in notas) + 15,3) 
				    FROM ambsuc WHERE Cod = pCodAmbSuc and position('<TiendaStockTN>' in notas) > 0)
					when pTienda = 'ML' then 
                   (SELECT SUBSTRING(notas, position('<TiendaStockML>' in notas) + 15,3) 
				    FROM ambsuc WHERE Cod = pCodAmbSuc and position('<TiendaStockML>' in notas) > 0)
				    else null
					end;
  RETURN id_tienda;
END;
$BODY$;

ALTER FUNCTION tienda_ecommerce.get_idtiendastocksucursal(character varying, character varying)
    OWNER TO abacouser;

---------------------------------

CREATE OR REPLACE FUNCTION tienda_ecommerce.get_idtiendasucursal(
	ptienda character varying,
	pcodambsuc character varying)
    RETURNS character varying
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
  UltimaOrden varchar;
  id_tienda   varchar;
BEGIN
  id_tienda := cast(case when pTienda = 'TN' then 
  					(SELECT SUBSTRING(notas, position('<TiendaNube>' in notas) + 12,3) 
					 FROM ambsuc WHERE Cod = pCodAmbSuc)
				    when pTienda = 'ML' then 
					(SELECT SUBSTRING(notas, position('<MercadoLibre>' in notas) + 14,3) 
					 FROM ambsuc WHERE Cod = pCodAmbSuc)
				    else '0'
					end 
			  as integer);
  id_tienda := coalesce(id_tienda,'0');
  RETURN id_tienda;
END;
$BODY$;

ALTER FUNCTION tienda_ecommerce.get_idtiendasucursal(character varying, character varying)
    OWNER TO abacouser;

--------------------------------

CREATE OR REPLACE FUNCTION tienda_ecommerce.get_jsonactualizacionstock(
	ptienda character varying,
	pidcomm integer,
	psigno integer,
	pcodambsuc character varying)
    RETURNS TABLE(xml character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 100

AS $BODY$
DECLARE
  id_tienda   varchar;
  cCursor    refCursor;
  vCadena       varchar;
  vCadenaNuevo  varchar;
  vArbolTN 		varchar;
BEGIN
	INSERT INTO IMPORTACION.IMPXML VALUES (0, 'xml');

  -- El id de la tienda está definido en la sucursal
  -- la TiendaStock de la sucursal puede redefinir la tienda de la que se saca el stock
  id_tienda := tienda_ecommerce.get_idtiendasucursal(pTienda, pCodAmbSuc);
  id_tienda := coalesce(tienda_ecommerce.get_idtiendastocksucursal(pTienda, pCodAmbSuc), id_tienda);
  
  vArbolTN  := case when pTienda = 'TN' then 
                        (SELECT TIPO FROM ARTA WHERE NOMBRE = 'SincTiendaNube')
    				when pTienda = 'ML' then 
                        (SELECT TIPO FROM ARTA WHERE NOMBRE = 'SincMercadoLibre')
                    else ''
					end;
  IF id_tienda IS NOT NULL AND vArbolTN IS NOT NULL THEN 
  	open cCursor for
  		SELECT '{"sku":' || trim(art.cod) || ',"stock":' || 
		trim(cast(artm.cant * artm.signo * psigno as varchar)) || '}'
    	FROM ARTM
    	INNER JOIN ART ON ART.ID = ARTM.IDART
		INNER JOIN ARTC ON ARTC.TIPOARTA = vArbolTN AND ARTC.IDART = ART.ID AND ARTC.CODARTR = 'STN' 
		INNER JOIN COMM ON COMM.ID = ARTM.IDCOMM
    	WHERE ARTM.IDCOMM = pIdComm AND (ARTM.MODIFICASTOCK = 'S' OR COMM.TIPOCOM = 'PTN');
		
  		vCadena := '[';
 
  	fetch cCursor into vCadenaNuevo;
  	while found loop
    	vCadena := vCadena || vCadenaNuevo || ',' ;  
     	fetch cCursor into vCadenaNuevo;
  	end loop;
  	close cCursor;
  	if length(vCadena) > 1 then
		vCadena := trim(vCadena,',');
		vCadena := vCadena || ']';
  	end if;
	IF vCadena <> '[' THEN
	    INSERT INTO IMPORTACION.IMPXML VALUES (1, 'SELECT CAST(''' || trim(id_tienda,'0') || '/act_stock/' || vCadena || ''' AS VARCHAR(64000)) as xml');
	   	RETURN QUERY EXECUTE ('SELECT CAST(''' || trim(id_tienda,'0') || '/act_stock/' || vCadena || ''' AS VARCHAR(64000)) as xml');
	  ELSE
	    INSERT INTO IMPORTACION.IMPXML VALUES (2, 'SELECT CAST(''' || trim(id_tienda,'0') || '/act_stock/' || vCadena || ''' AS VARCHAR(64000)) as xml');
		RETURN QUERY EXECUTE ('SELECT CAST('''' AS VARCHAR(64000)) as xml');
	END IF;
  ELSE
	    INSERT INTO IMPORTACION.IMPXML VALUES (3, 'SELECT CAST(''' || trim(id_tienda,'0') || '/act_stock/' || vCadena || ''' AS VARCHAR(64000)) as xml');
	RETURN QUERY EXECUTE ('SELECT CAST('''' AS VARCHAR(64000)) as xml');
  END IF; 
END;
$BODY$;

ALTER FUNCTION tienda_ecommerce.get_jsonactualizacionstock(character varying, integer, integer, character varying)
    OWNER TO abacouser;

---------------------------------

CREATE OR REPLACE FUNCTION tienda_ecommerce.get_ultimaordenprocesada(
	ptienda character varying,
	ptipocom character varying,
	pcodambsuc character varying,
	strxml character varying)
    RETURNS TABLE(xml character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 100

AS $BODY$
DECLARE
  UltimaOrden varchar;
  id_tienda   integer;
BEGIN
  id_tienda := cast(tienda_ecommerce.get_IdTiendaSucursal(pTienda, pCodAmbSuc) as integer);
  UltimaOrden := (SELECT SUBSTRING(notas, position('<UltimaOrden' || pTienda || '>' in notas) + 15,5)
					 FROM ambsuc WHERE Cod = pCodAmbSuc);

  UltimaOrden := COALESCE((SELECT MAX(txtlibre2) FROM COMM WHERE TIPOCOM = pTipoCom 
						   AND cast(tienda_ecommerce.get_IdTiendaSucursal(pTienda, comm.codAmbSuc) as integer) = id_tienda),
						   COALESCE(UltimaOrden,'0'),'0');
  UltimaOrden := CAST(CAST(UltimaOrden as integer) + 1 AS VARCHAR);

  RETURN QUERY EXECUTE ('SELECT CAST(''' || cast(id_tienda as varchar) || '/orders?q=' || UltimaOrden || ''' AS VARCHAR(64000)) as xml');
END;
$BODY$;

ALTER FUNCTION tienda_ecommerce.get_ultimaordenprocesada(character varying, character varying, character varying, character varying)
    OWNER TO abacouser;

----------------------------------

-- FUNCTION: tienda_ecommerce.procesar_facturas(character varying, character varying, character varying)

-- DROP FUNCTION tienda_ecommerce.procesar_facturas(character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION tienda_ecommerce.procesar_facturas(
	ptienda character varying,
	pcodambsuc character varying,
	parametros character varying)
    RETURNS TABLE(xml character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
DECLARE
  paquete varchar;
  i_registro integer;
  i_producto integer;
  
  body json;
  orden json;
  producto json;
  productos json;
  
  vemail varchar;
  vIdPad integer;
  vIdOrden varchar;
  vNroOrden varchar;
  vFecha varchar;
  vstatus varchar;
  vestadodelaorden varchar;
  vestadodelpago varchar;
  vestadodelenvio varchar;
  vflete 		  varchar;
  vnombredelcomprador varchar;
  vdnicuit varchar;
  vtelefono varchar;
  vnombreparaelenvio varchar;
  vtelefonoparaelenvio varchar;
  vdireccionparaelenvio varchar;
  vdomicilioparaelenvio varchar;
  vnumeroparaelenvio varchar;
  vciudadparaelenvio varchar;
  vlocalidadparaelenvio varchar;
  vpisoparaelenvio varchar;
  vprovinciaparaelenvio varchar;
  vcodigopostalparaelenvio varchar;
  vmediodeenvioparaelenvio varchar;
  vmediodepagoparaelenvio varchar;
  vnota  varchar;
  vshipping_option  varchar;
  VGatewayDePago    	varchar;
  VGatewayReferencia 	varchar;

  vLeyenda  varchar;

  vIdArt     varchar;
  vCantidad  varchar;
  vPrecio    varchar; 
  vSKU       varchar;
  vCodStkDep varchar;
 
  vIdArtDesconocido integer;
  vIdPadCF          integer;
  pTipoComF         varchar;
  pTipoComP         varchar;
  vTipoCom          varchar; 
  vIdArtFlete       integer;
  vDateTimeOrigen varchar;
  
  vPagado    boolean;
  vTipoValT   varchar;
  vCodPadRAcc varchar;
  vIdPadMercadoLibre integer;
  vXML  varchar;

BEGIN
--  insert into importacion.impxml VALUES (1,parametros,null, null, now(), null, 'DUMMY');

  TRUNCATE TABLE importacion.impxml;

  pTipoComF := 'TZAP';
  pTipoComP := 'PTN';
  vIdPadCF  := 26;
  vIdArtFlete := 749;
  vIdArtDesconocido := COALESCE((SELECT ID FROM ART WHERE COD = 'ERROR_TN'),-1);
  vCodStkDep := COALESCE((SELECT CODSTKDEP FROM AMBSUC WHERE AMBSUC.COD = pCodAmbSuc),'D0');
  vIdPadMercadoLibre := 3018;
 
  vTipoValT   := 'MP-M';
  vCodPadRAcc := 'F.MPE.MP';
 
  parametros := str_sin_acentos(parametros);
  paquete := replace(parametros,'--#','}');
  paquete := replace(paquete,'#--','{');
  paquete := replace(paquete,'@@@',':');

  insert into importacion.impxml VALUES (2,paquete,null, now(), null, 'DUMMY');

  body := json_extract_path(cast(paquete as json),'body');
  i_registro := 0;
  
  vXML = 'FIN';
  if (json_array_length(body) > 0) then vXML :=  '<Movimientos>';
  end if;
  
  WHILE i_registro <=  json_array_length(body) - 1 LOOP
     orden  := body -> i_registro;
	 vIdOrden  := orden ->> 'id';
	 vNroOrden := orden ->> 'number';
	 vemail := orden -> 'customer' ->> 'email';
	 vIdPad := coalesce((SELECT id FROM PAD where (accesosucursales ilike '%' || pCodambsuc || '%' or accesosucursales = '*') and email = vemail limit 1),vIdPadCf);
	 vFecha := substring(orden ->> 'created_at',1,10); -- 2021-12-05
	 vestadodelaorden  := orden ->> 'status';
	 vestadodelpago  := orden ->> 'payment_status'; 
	 vestadodelenvio := orden ->> 'shipping_status';
	 vflete          := orden ->> 'shipping_cost_customer';
	 vnombredelcomprador := orden -> 'customer' ->>'name';
	 vdnicuit  := orden -> 'customer' ->> 'identification';
	 vtelefono := orden -> 'customer' ->> 'phone';
	 vnombreparaelenvio    := orden -> 'shipping_address' ->>'name';
	 vtelefonoparaelenvio  := orden -> 'shipping_address' ->>'phone';
	 vdomicilioparaelenvio := orden -> 'shipping_address' ->>'address';
	 vnumeroparaelenvio    := orden -> 'shipping_address' ->>'number';
	 vciudadparaelenvio    := orden -> 'shipping_address' ->>'city';
	 vlocalidadparaelenvio := orden -> 'shipping_address' ->>'locality';
	 vpisoparaelenvio      := orden -> 'shipping_address' ->>'floor';
	 vprovinciaparaelenvio := orden -> 'shipping_address' ->>'province';
	 vcodigopostalparaelenvio := orden -> 'shipping_address' ->>'zipcode';
	 vmediodeenvioparaelenvio := orden ->> 'shipping';
	 vmediodepagoparaelenvio  := orden ->  'payment_details' ->>'method';
	 vnota         			  := str_sin_signos(orden ->> 'note');
	 vshipping_option         := orden ->> 'shipping_option';
	 vGatewayDePago           := orden ->> 'gateway';
	 vGatewayReferencia       := orden ->> 'gateway_id';
	 vDateTimeOrigen		  := orden ->> 'created_at';
	 
	 vPagado := (vGatewayDePago = 'mercadopago') and (vestadodelpago = 'paid'); --'xxmercadopago'); offline

     IF vPagado OR TRUE THEN  -- Si no está pagado no se procesa.
 		vTipoCom := pTipoComP;
	--		ELSE
	--		   vTipoCom := pTipoComP;
	--		END IF;
	--
-- insert into importacion.impxml VALUES (2,vNroOrden || vGatewayDePago || vestadodelpago || coalesce(vFlete,'fleto'), null, now(), null, 'DUMMY');
 
     vLeyenda :=  'Envio: ' || vshipping_option || chr(10) ||
	          'email: ' || str_sin_acentos(vemail) || chr(10) ||
			  'Orden: ' || str_sin_acentos(vestadodelaorden) || chr(10) ||
			  'Pago : ' || str_sin_acentos(vestadodelpago)   || chr(10) ||
			  'Envio: ' || str_sin_acentos(vestadodelenvio)  || chr(10) ||
			  CASE WHEN vIdPad IS NULL THEN
			      'Nombre: ' ||   str_sin_acentos(vnombredelcomprador) || chr(10) ||
			      'DNI/CUIT: ' || str_sin_acentos(vdnicuit) || chr(10) ||
			      'Telefono: ' || vtelefono || chr(10) 
		      ELSE '' END ||
			  'Pagado por: ' || str_sin_acentos(vGatewayDePago) || ' Ref: ' || coalesce(vGatewayReferencia,'') || chr(10) ||
			  'Nombre envio: ' || str_sin_acentos(vnombreparaelenvio) || chr(10) ||
			  'Telefono envio: ' || vtelefonoparaelenvio              || chr(10) ||
       		  'Direccion: ' || str_sin_acentos(vdomicilioparaelenvio) || ' Nro: ' || vnumeroparaelenvio || 
			  ' Piso: ' || vpisoparaelenvio || chr(10) ||
			  'Localidad: ' || str_sin_acentos(vlocalidadparaelenvio) || ' Ciudad: ' || str_sin_acentos(vciudadparaelenvio) || chr(10) || 
			  'C.Postal: '  || vcodigopostalparaelenvio || ' Provincia: ' || vprovinciaparaelenvio || chr(10) || 
              'Envio: ' || str_sin_acentos(vmediodeenvioparaelenvio) || chr(10) || 
			  'Medio de pago: ' || str_sin_acentos(vmediodepagoparaelenvio) || chr(10) || 
			  'Nota: ' || str_sin_acentos(vnota);

			vXML := vXML || '<Comprobante TipoCom = "' 
			             ||  vTipoCom  
                         || '"  Referencia = "' || CAST(vidpad AS VARCHAR) || '">' || 
						 '<Encabezado>' || 
                         '<IdPad>'   || CAST(vidpad AS VARCHAR) || '</IdPad><Fecha> ' || to_char(cast(vfecha as date), 'DD/MM/YY') || '</Fecha>' ||
                         '<Leyenda>' || vLeyenda || '</Leyenda>' ||
						 '<txtlibre1>' || vIdOrden || '</txtlibre1>' ||
						 '<txtlibre2>' || vNroOrden || '</txtlibre2>' ||
						 '<txtlibre3>' || vDateTimeOrigen || '</txtlibre3>' ||
						 '<detalle> Orden: ' || vNroOrden || '</detalle>' ||
						 '<GeneradoPor>ImpTN</GeneradoPor><CodAmbSuc>' || pCodAmbSuc || '</CodAmbSuc>';
		--	vXML := vXML || '<IdCtbCos1>2</IdCtbCos1>';
			vXML := vXML || '</Encabezado><Renglones>';

			--i_registro := i_registro + 1;

            productos := orden -> 'products';
			i_producto := 0;
			WHILE i_producto <= json_array_length(productos) - 1 LOOP
				producto := productos -> i_producto;
				vIdArt    := cast(coalesce((SELECT ID FROM ART WHERE COD = producto ->> 'sku'),vIdArtDesconocido) as varchar);
				vCantidad := producto ->> 'quantity';
				vPrecio   := producto ->> 'price';
				vSKU      := producto ->> 'sku';
				
				vXML := vXML || 
				   CASE WHEN false  THEN '<RenglonArticulo Signo = "Entrada">' 
                                    ELSE '<RenglonArticulo Signo = "Salida">'
                   END || 
                   '<IdArt> '  || vIDART    || '</IdArt>' ||
                   '<Cant>'    || vCANTIDAD || '</Cant>';
                 vXML := vXML || '<PrecioCIva>'  || vprecio || '</PrecioCIva>';
                 vXML := vXML || '<PrecioLista>' || vprecio || '</PrecioLista>';
                 vXML := vXML || '<Descripgrande>' || coalesce(vSKU,'') || '</Descripgrande>';
				 IF cast(vIdArt as integer) = vIdArtDesconocido THEN 
				    vXML := vXML || '<Descrip>' || coalesce(vSKU,'') || '</Descrip>';
				 END IF;
                 vXML := vXML || '<CodStkDep>' || vCodStkDep || '</CodStkDep>';
				-- vXML := vXML || '<IdCtbCos1>2</IdCtbCos1>';
                 vXML := vXML || '</RenglonArticulo>';
				 i_producto := i_producto + 1;
			END LOOP;
*comentario			IF vFlete is not null THEN
                 vXML := vXML || '<RenglonArticulo Signo = "Salida">' ||
			            '<IdArt> '  || cast(vIdArtFlete as varchar)    || '</IdArt>' ||
            		    '<Cant>1</Cant>';
                 vXML := vXML || '<PrecioCIva>'  || vFlete || '</PrecioCIva>';
                 vXML := vXML || '<CodStkDep>' || vCodStkDep || '</CodStkDep>';
				 vXML := vXML || '<IdCtbCos1>2</IdCtbCos1>';
                 vXML := vXML || '</RenglonArticulo>';
			END IF;
*fin comentario		-- Si fue pagado cargo la forma de pago  
*comentario			IF vPagado THEN
			   vXML := vXML || '<RenglonFondo Signo = "Entrada" ImpoAutomatico="S">';
			   vXML := vXML || '<TipoValT>' || vTipoValT || '</TipoValT>';
			   vXML := vXML || '<TipoAcc>I</TipoAcc>';
			   vXML := vXML || '<CodPadrAcc>' || vCodPadrAcc || '</CodPadrAcc>';
			   vXML := vXML || '<idPad>' || cast(vIdPadMercadoLibre as varchar) || '</idPad>';
			   vXML := vXML || '</RenglonFondo>';
			END IF;	
*fin comentario
			vXML := vXML || '</Renglones>';
			vXML := vXML || '</Comprobante>';
	    END IF;
   	    i_registro := i_registro + 1;
  	  END LOOP;
	  if (vXML <> 'FIN') then
	      vXML := vXML || '</Movimientos>';
	  end if;
	  vXML := public.str_sin_acentos(vXML);
      insert into importacion.impxml VALUES (3,utf8_to_latin1(vxml),null, now(), null, 'DUMMY');
 		IF vXML is NULL THEN
		   RETURN QUERY EXECUTE ('SELECT CAST('''' AS VARCHAR) AS XML');
		ELSE
		   RETURN QUERY EXECUTE ('SELECT cast(' || '''' || utf8_to_latin1(vxml) || '''' || ' as varchar) as XML');
		END IF;

END;
$BODY$;

ALTER FUNCTION tienda_ecommerce.procesar_facturas(character varying, character varying, character varying)
    OWNER TO abacouser;

*/
--
SELECT aaVersion('502.03','502.02');
--

